package com.nine.dao.sql;


import com.nine.dao.annotation.Column;
import com.nine.dao.dto.*;


import org.apache.ibatis.jdbc.SQL;
import org.apache.log4j.Logger;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;


import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;


/**
 * 个人改造后的通用dao层类，返回一些简单的sql语句 = = (自制组件)
 *
 * @author zjb
 */


public class BaseSQLToString<T> {

    private static Logger log = Logger.getLogger(BaseSQLToString.class.getClass());


    /**
     * 处理更新
     *
     * @param dataBaseName 数据库名
     * @param query        查询参数
     * @param valueDtos    更新的键值对
     * @return
     */
    public String updateExample(final String dataBaseName, BaseDaoQuery query, final List<Map<String, Object>> valueDtos) {
        this.checkListNull(valueDtos);
        String sqlone = new SQL() {{
            UPDATE(dataBaseName);
            for (Map<String, Object> keyValueDto : valueDtos) {
                SET(keyValueDto.get("title") + " = '" + keyValueDto.get("value") + "'");
            }
        }}.toString();
        if (query != null) {
            String sqltwo = this.handleWhereExample(query);
            sqlone = sqlone.concat(" " + sqltwo);
        }
        log.info(sqlone.replace("\r\n", " "));

        return sqlone;
    }

    /**
     * 处理删除
     *
     * @param dataBaseName 数据库名
     * @param query        查询参数
     * @return
     */
    public String deleteExample(final String dataBaseName, BaseDaoQuery query) {
        String sqlone = new SQL() {{
            DELETE_FROM(dataBaseName);
        }}.toString();
        if (query != null) {
            String sqltwo = this.handleWhereExample(query);
            sqlone = sqlone.concat(" " + sqltwo);
        }
        log.info(sqlone.replace("\r\n", " "));
        return sqlone;

    }

    /**
     * 处理插入
     *
     * @param dataBaseName 数据库名称
     * @param entity       插入的实体
     * @return
     */
    public String insertExample(final String dataBaseName, final Object entity) throws IllegalAccessException {
//        this.checkListNull(valueDtos);
        // TODO: 2021/6/8 严谨一些这里需要根据每一个类别去赋值，而不是一股脑全都转成String
        String sqlOne = new SQL() {{
            INSERT_INTO(dataBaseName);
            for (Field field : entity.getClass().getDeclaredFields()) {
                field.setAccessible(true);
                String value = field.get(entity) + "";
                if (!StringUtils.isEmpty(field.get(entity))) {
                    if (field.getType() == Date.class) {
                        //时间类型默认转换为yyyy-MM-dd HH:mm:ss格式
                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        value = df.format(field.get(entity));

                    }
                    //获取变量上的注释，实现与数据库表名称的映射
                    Column column = field.getAnnotation(Column.class);
                    if (column != null) {
                        VALUES(column.name(), "'" + value + "'");
                    } else {
                        VALUES(field.getName(), "'" + value + "'");
                    }
                }

            }
//            for (Map<String,Object> keyValueDto : valueDtos) {
//                VALUES(keyValueDto.get("title")+"", "'" + keyValueDto.get("value") + "'");
//            }
        }}.toString();
        log.info(sqlOne.replace("\r\n", " "));
        return sqlOne;

    }

    /**
     * 处理查询
     *
     * @param query        处理where
     * @param dataBaseName 查询的库，内连接，如果需要外连接之类的则不支持
     * @param result       查询返回的字段,为空，则返回全部
     * @param page         是否按分页查询，limit
     * @return
     */
    public String selectExample(final BaseDaoQuery query, final List<String> dataBaseName, final List<String> result, Page page) {
        this.checkListNull(dataBaseName);
        String sqlone = new SQL() {{
            if (result == null || result.isEmpty()) {
                SELECT("*");
            } else {
                for (String res : result) {
                    SELECT(res);
                }
            }
            for (String db : dataBaseName) {
                FROM(db);
            }
        }}.toString();
        if (query != null) {
            String sqltwo = this.handleWhereExample(query);
            sqlone = sqlone.concat(" " + sqltwo);
        }
        if (query != null && !StringUtils.isEmpty(query.getOrderByClause())) {
            String sqlOrder = this.handleOrderBy(query.getOrderByClause(), query.getOrderByType());
            sqlone = sqlone.concat(" " + sqlOrder);
        }
        if (page != null) {
            String sqlthree = this.handleLimitPage(page);
            sqlone = sqlone.concat(" " + sqlthree);
        }
        log.info(sqlone.replace("\r\n", " "));
        return sqlone;
    }

    /**
     * 处理分页查询 limit sql语句
     * 这里page的no要-1
     *
     * @param page
     * @return
     */
    protected String handleLimitPage(Page page) {

        return "LIMIT " + ((page.getPageNo() - 1) * page.getPageSize()) + ", " + page.getPageSize();
    }

    /**
     * 处理排序
     *
     * @param column 需要排序的字段
     * @param sort   升序还是降序，1升序，2降序
     * @return
     */
    protected String handleOrderBy(String column, String sort) {
        Assert.notNull(column, "排序字段不能为空");
        return "ORDER BY " + column + " " + sort;
    }

    /**
     * 通过example处理sql中的where语句
     *
     * @return
     */
    public String handleWhereExample(final BaseDaoQuery query) {
        if (query.getOredCriteria() != null && !query.getOredCriteria().isEmpty()) {
            StringBuilder whereSql = new StringBuilder("where");
            int cuont1 = 1;
            for (Criteria criteria : query.getOredCriteria()) {
                if (criteria.isValid()) {
                    whereSql.append("(");
                    //用来判断查询个数
                    int count2 = 1;
                    for (Criterion c : criteria.getCriteria()) {
                        if (c.isNoValue()) {
                            whereSql.append(c.getCondition());
                        } else if (c.isSingleValue()) {
                            whereSql.append(c.getCondition() + "'" + c.getValue() + "'");
                        } else if (c.isBetweenValue()) {
                            whereSql.append(c.getCondition() + "'" + c.getValue() + "' and '" + c.getSecondValue() + "'");
                        } else if (c.isListValue()) {
                            List<String> ob = (List<String>) c.getValue();
                            StringBuilder sqllist = new StringBuilder("(");
                            int i = 1;
                            for (String o : ob) {
                                if (i == ob.size()) {
                                    sqllist.append("'" + o + "')");
                                } else {
                                    sqllist.append("'" + o + "',");
                                }
                                i++;
                            }
                            whereSql.append(c.getCondition() + sqllist);
                        }

                        if (count2 != criteria.getCriteria().size()) {
                            whereSql.append(" and ");
                        }
                        count2++;
                    }

                    whereSql.append(")");

                    if (cuont1 != query.getOredCriteria().size()) {
                        whereSql.append(" or ");
                    }
                    cuont1++;
                }
            }
            return whereSql.toString();
        }

        return "";

    }

    private boolean checkListNull(List object) {
        if (object == null || object.isEmpty()) {
            throw new RuntimeException("参数不能为空");
        }
        return true;
    }
}
